package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import util.JDBCUtil;
import domain.ActorJob;
import domain.CrewJob;
import domain.User;
import ex.DatabaseException;

public class WorkedAsCrewDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String CREATE_WORKED_AS_CREW= "insert into worked_as_crew (uid,mid,status,cjid) values (?,?, ?, ?) ";
	private static final String SELECT_WORKED_AS_CREW_BY_CJID = "select * from worked_as_crew where cjid = ?";
	private static final String EDIT_STATUS_OF_WORKED_AS_CREW_BY_UID_AND_CJID = "update worked_as_crew set status=? where uid=? and cjid=?";
	private static final String DELETE_WORKED_BY_CJID = "delete from worked_as_crew  where cjid=?";
	// public

	UserDAO udao = new UserDAO();

	public void insertWorkedAsCrew(User user, CrewJob crewJob)
			throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_WORKED_AS_CREW);
			stmt.setLong(1, user.getUid());
			stmt.setLong(2, crewJob.getMid());
			stmt.setInt(3, 0);
			// stmt.setString(4, );
			stmt.setLong(4, crewJob.getCjid());

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
	}
	
	public Map<User,Integer> selectWorkedAsCrewUsersByCjid(long cjid)
			throws DatabaseException {
		 Map<User,Integer> users = new HashMap<User, Integer>();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_WORKED_AS_CREW_BY_CJID);
			stmt.setLong(1, cjid);

			rs = stmt.executeQuery();
			while (rs.next()) {
				long uid = rs.getLong("uid");
				int s=rs.getInt("status");
				User user = udao.selectUserByUid(uid);
				if (user != null)
					users.put(user, s);
			}
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
		return users;
	}

	public void acceptApply(User user, CrewJob cj) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(EDIT_STATUS_OF_WORKED_AS_CREW_BY_UID_AND_CJID);
			stmt.setInt(1, 1);
			stmt.setLong(2, user.getUid());
			stmt.setLong(3, cj.getCjid());

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
	}

	public void rejectApply(User user,  CrewJob cj) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(EDIT_STATUS_OF_WORKED_AS_CREW_BY_UID_AND_CJID);
			stmt.setInt(1, -1);
			stmt.setLong(2, user.getUid());
			stmt.setLong(3, cj.getCjid());

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
	}

	public Map<User, Integer> selectWorkedUsersByCjid(long cjid) throws DatabaseException {
		Map<User, Integer> users = new HashMap<User, Integer>();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_WORKED_AS_CREW_BY_CJID);
			stmt.setLong(1, cjid);

			rs = stmt.executeQuery();
			while (rs.next()) {
				long uid = rs.getLong("uid");
				int s = rs.getInt("status");
				User user = udao.selectUserByUid(uid);
				if (user != null) {
					users.put(user, s);
				}
			}
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
		return users;
	}
	
	public void deleteWorkedAsCrewByCjid(long cjid) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(DELETE_WORKED_BY_CJID);
			stmt.setLong(1, cjid);			

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
	}
}
